﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SnailGame.Utility.DataAccess;
using System.Data;
using System.Configuration;
using System.Collections;
using Com.Crc.PoJo.Model.Resource;

namespace Com.Crc.ResManage.DAL.EquipManage
{
    public class OLTManagerDAL
    {
        DataBroker broker = new DataBroker();

        /// <summary>
        /// 删除设备，修改状态位为0
        /// </summary>
        /// <param name="wlanList"></param>
        /// <returns></returns>
        public bool DeleteOLTData(ArrayList OLTList)
        {
            string id = "";
            for (int i = 0; i < OLTList.Count; i++)
            {
                id += OLTList[i] + ",";
            }
            id = id.Substring(0, id.Length - 1);
            string strSql = @"UPDATE TT_RESOURCE_OLT SET S_STATE='0' WHERE N_ID IN (" + id + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 修改OLT设备信息
        /// </summary>
        /// <param name="data">model实体</param>
        /// <returns></returns>
        public bool UpdateOLTData(OLTEntity data)
        {
            string strSql = @" UPDATE TT_RESOURCE_OLT SET
                                      N_SUBSTATION_ID=" + data.N_SUBSTATION_ID + @",
                                      N_DEP_BRANCH_ID=" + data.N_DEP_BRANCH_ID + @",
                                      N_UNITTYPE_ID=" + data.N_UNITTYPE_ID + @",
                                      S_NAME='" + data.S_NAME + @"',
                                      S_ADDRESS='" + data.S_ADDRESS + @"',
                                      N_VLAN=" + data.N_VLAN + @",
                                      N_UP_LEVEL=" + data.N_UP_LEVEL + @",
                                      N_UP_BAS_ID=" + data.N_UP_BAS_ID + @",
                                      N_UP_BAS_PORT=" + data.N_UP_BAS_PORT + @",
                                      N_UP_EXCHANGE_ID=" + data.N_UP_EXCHANGE_ID + @",
                                      N_UP_EXCANGE_PORT=" + data.N_UP_EXCANGE_PORT + @",
                                      S_BAK='" + data.S_BAK + @"',
                                      D_DATE = '" + data.D_DATE.ToString("yyyy-MM-dd HH:mm:ss") + @"'
                                WHERE N_ID=" + data.N_ID;
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }

        /// <summary>
        /// 新增OLT设备信息
        /// </summary>
        /// <param name="strBranchID">分局ID</param>
        /// <param name="strOfficeID">局点ID</param>
        /// <param name="strEquipID">设备ID</param>
        /// <returns></returns>
        public bool AddOLTData(OLTEntity data)
        {
            string strSql = @" INSERT INTO TT_RESOURCE_OLT(N_SUBSTATION_ID,
                                                            N_DEP_BRANCH_ID,
                                                            N_UNITTYPE_ID,
                                                            S_NAME,
                                                            S_ADDRESS,
                                                            N_VLAN,
                                                            N_UP_LEVEL,
                                                            N_UP_BAS_ID,
                                                            N_UP_BAS_PORT,
                                                            N_UP_EXCHANGE_ID,
                                                            N_UP_EXCANGE_PORT,
                                                            S_BAK,
                                                            S_STATE,
                                                            D_DATE)
                                    VALUES (" + data.N_SUBSTATION_ID + "," +
                                                data.N_DEP_BRANCH_ID + "," +
                                                data.N_UNITTYPE_ID + "," +
                                                "'" + data.S_NAME + "'," +
                                                "'" + data.S_ADDRESS + "'," +
                                                data.N_VLAN + "," +
                                                data.N_UP_LEVEL + "," +
                                                data.N_UP_BAS_ID + "," +
                                                data.N_UP_BAS_PORT + "," +
                                                data.N_UP_EXCHANGE_ID + "," +
                                                data.N_UP_EXCANGE_PORT + "," +
                                                "'" + data.S_BAK + "'," +
                                                "'" + data.S_STATE + "'," +
                                                "'" + data.D_DATE.ToString("yyyy-MM-dd HH:mm:ss") + "'" + ")";
            return broker.ExecuteForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }
        /// <summary>
        /// 查询OLT设备信息
        /// </summary>
        /// <param name="strBranchID">分局ID</param>
        /// <param name="strOfficeID">局点ID</param>
        /// <param name="strEquipID">设备ID</param>
        /// <returns></returns>
        public DataTable QueryOLTData(string strBranchID, string strOfficeID, string strOLTID)
        {
            string strSql = @" SELECT a.N_ID,
                                      b.S_NAME AS S_SUBSTATION_NAME,
                                      a.N_SUBSTATION_ID,
                                      c.S_NAME AS S_BRANCH_NAME,
                                      a.N_DEP_BRANCH_ID,
                                      d.S_NAME AS S_OLTTYPE,
                                      a.N_UNITTYPE_ID,
                                      a.S_NAME,
                                      a.S_ADDRESS,
                                      a.N_VLAN,
                                      e.S_NAME AS S_BANDWIDTH_TYPE,
                                      a.N_UP_LEVEL,
                                      f.S_NAME AS S_BAS_TYPE,
                                      a.N_UP_BAS_ID,
                                      a.N_UP_BAS_PORT,
                                      g.S_NAME AS S_EXCHANGE_NAME,
                                      a.N_UP_EXCHANGE_ID,
                                      a.N_UP_EXCANGE_PORT,
                                      a.S_BAK,
                                      a.S_STATE,
                                      a.D_DATE 
                                 FROM TT_RESOURCE_OLT a
                           INNER JOIN TT_RESOURCE_SUBSTATION b ON a.N_SUBSTATION_ID = b.N_ID
                           INNER JOIN TT_RESOURCE_DICTIONARY c ON a.N_DEP_BRANCH_ID = c.N_ID AND c.S_TYPE=" + (int)Dictionary.COMMON_DIC_TYPE.RESOURCE_WLAN_BRANCH_TYPE + @"
                           INNER JOIN TT_RESOURCE_DICTIONARY d ON a.N_UNITTYPE_ID = d.N_ID AND d.S_TYPE=" + (int)Dictionary.COMMON_DIC_TYPE.RESOURCE_OLT_EQUIPMENT_TYPE + @"
                           INNER JOIN TT_RESOURCE_DICTIONARY e ON a.N_UP_LEVEL = e.N_ID AND e.S_TYPE=" + (int)Dictionary.COMMON_DIC_TYPE.RESOURCE_WLAN_UP_LEVEL_TYPE + @"
                           INNER JOIN TT_RESOURCE_DICTIONARY f ON a.N_UP_BAS_ID = f.N_ID AND f.S_TYPE=" + (int)Dictionary.COMMON_DIC_TYPE.RESOURCE_WLAN_UP_BAS_TYPE + @"
                           INNER JOIN TT_RESOURCE_EXCHANGE g ON a.N_UP_EXCHANGE_ID = g.N_ID
                                WHERE a.S_STATE='1' ";
            if (strBranchID != "")
            {
                strSql += " AND a.N_DEP_BRANCH_ID = " + strBranchID;
            }
            if (strOfficeID != "")
            {
                strSql += " AND a.N_SUBSTATION_ID = " + strOfficeID;
            }
            if (strOLTID != "")
            {
                strSql += " AND a.N_ID = " + strOLTID;
            }
            return broker.ExecuteQueryForOleDB(strSql, ConfigurationManager.ConnectionStrings["CRCAppConn"].ToString());
        }
    }
}
